/*******************************************************************************
 * Copyright (c) 2009-2011 ETCORE Labs. All rights reserved. 
 * 
 * This software is the confidential and proprietary information of ETCORE Labs.
 * You shall not disclose such confidential information and shall use it
 * only in accordance with the terms of the license agreement you entered into
 * with ETCORE Labs. 
 * 
 * Support can be obtained from support forums at:  http://www.etcore.com/
 * /
 *******************************************************************************/
package etcore.webapp.grouptalk.user.dao;

import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import etcore.webapp.grouptalk.common.dao.BaseDAO;
import etcore.webapp.grouptalk.common.dao.HibernateSessionFactory;
import etcore.webapp.grouptalk.user.object.Favourite;

/**
 * An instance of FavouriteDAO is a data access object used to access Favourite,
 * which contains query and update operations
 * 
 * @author ETCORE GroupTalk Developers
 * 
 */
public class FavouriteDAO extends BaseDAO {
	/**
	 * Find Favourite instances
	 * 
	 * @param begin
	 *            start position
	 * @param length
	 *            count of instances
	 * @return
	 */
	public List<Favourite> findAll(int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Favourite.class.getSimpleName());
		sqlStringBuffer.append(" order by id desc");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		@SuppressWarnings("unchecked")
		List<Favourite> results = queryObject.list();
		return results;

	}

	/**
	 * Find the count of Favourite instances
	 * 
	 * @return count of Favourite instances
	 */
	public long findAllSize() {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("select count(*) from ");
		sqlStringBuffer.append(Favourite.class.getSimpleName());
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		Object results = queryObject.uniqueResult();
		return (results == null) ? 0 : ((Number) results).longValue();

	}

	/**
	 * Find the Favourite instance by the userName and message id
	 * 
	 * @param userName
	 *            userName
	 * @param mid
	 *            message id
	 * @return
	 */
	public Favourite findBy2Properties(String userName, Integer mid) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Favourite.class.getSimpleName());
		sqlStringBuffer
				.append(" as model where model.userName = ? and model.mid=?");

		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, userName);
		queryObject.setParameter(1, mid);
		@SuppressWarnings("rawtypes")
		List results = queryObject.list();
		if (results != null && results.size() > 0)
			return (Favourite) results.get(0);
		return null;

	}

	/**
	 * Find Favourite instance by id
	 * 
	 * @param id
	 *            Favourite id
	 * @return Favourite instance
	 */
	public Favourite findById(java.lang.Integer id) {
		Session session = HibernateSessionFactory.getSession();
		Favourite instance = (Favourite) session.get(Favourite.class.getName(),
				id);
		return instance;

	}

	/**
	 * Find Favourite instances by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @return Favourite instances
	 */
	public List<Favourite> findByProperty(String propertyName, Object value) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Favourite.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append("= ?");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		@SuppressWarnings("unchecked")
		List<Favourite> results = queryObject.list();
		return results;

	}

	/**
	 * Find Favourite instances by a porperty value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @param begin
	 *            start position
	 * @param length
	 *            count of instances
	 * @return Favourite instances
	 */
	public List<Favourite> findByProperty(String propertyName, Object value,
			int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Favourite.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append("= ?");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		@SuppressWarnings("unchecked")
		List<Favourite> results = queryObject.list();

		return results;

	}

	public long findSizeOfAdminFavourites() {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT COUNT(f.mid) as ct FROM gt_favourite f, gt_member m,gt_group g WHERE g.name='ROOTS' AND g.id=m.gid AND m.userName=f.userName";

	 

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);

		// queryObject.setParameter(0, g);
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}
	public long findSizeOfAdminFavourites(String propertyName,Object value) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT COUNT(f.mid) as ct FROM gt_favourite f, gt_message msg,gt_member m,gt_group g WHERE g.name='ROOTS' AND g.id=m.gid AND m.userName=f.userName and msg.id=f.mid and msg."+propertyName+"=?";

	 

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);

		sqlQuery.setParameter(0, value);
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}
	public long findSizeOfAdminFavourites(String inputDate) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT COUNT(f.mid) as ct FROM gt_favourite f, gt_member m,gt_group g WHERE g.name='ROOTS' AND g.id=m.gid AND m.userName=f.userName and (TO_DAYS("+inputDate+")-TO_DAYS(f.addtime))=0 ";

	 

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);

		// queryObject.setParameter(0, g);
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}
	public long findSizeOfAdminFavourites(String inputDate,String propertyName,Object value) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT COUNT(f.mid) as ct FROM gt_message msg,gt_favourite f, gt_member m,gt_group g WHERE g.name='ROOTS' AND g.id=m.gid AND m.userName=f.userName and (TO_DAYS("+inputDate+")-TO_DAYS(f.addtime))=0 and msg.id=f.mid and msg."+propertyName+"=?";

	 

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);

		sqlQuery.setParameter(0, value);
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}
	/**
	 * Find Favourite instances displayed in English
	 * 
	 * @param begin
	 *            start position
	 * @param length
	 *            the count of instances
	 * @return Favourite instances
	 */
	@SuppressWarnings("rawtypes")
	public List findEnglishFavourites(int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "";

		queryString = "select    fmodel.addTime,fmodel.userName, model.id, model.name,gt_user.avatar  from gt_favourite as fmodel, gt_message as model,gt_topic as tmodel, gt_group as gmodel,gt_user where gt_user.userName=fmodel.userName and fmodel.mid=model.id and model.tid=tmodel.id and tmodel.gid=gmodel.id and gmodel.en=1 and STRCMP(now(),fmodel.addtime)>=0 order by fmodel.id desc";

		Query queryObject = session.createSQLQuery(queryString);

		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List results = queryObject.list();

		return results;

	}

	/**
	 * Find Favourite instances displayed in the local language
	 * 
	 * @param begin
	 *            start position
	 * @param length
	 *            the count of instances
	 * @return Favourite instances
	 */
	@SuppressWarnings("rawtypes")
	public List findLocalFavourites(int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "";

		queryString = "select    fmodel.addTime,fmodel.userName, model.id, model.name,gt_user.avatar  from gt_favourite as fmodel, gt_message as model,gt_topic as tmodel, gt_group as gmodel,gt_user where gt_user.userName=fmodel.userName and fmodel.mid=model.id and model.tid=tmodel.id and tmodel.gid=gmodel.id and gmodel.local=1 and STRCMP(now(),fmodel.addtime)>=0 order by fmodel.id desc";

		Query queryObject = session.createSQLQuery(queryString);

		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List results = queryObject.list();

		return results;

	}

	/**
	 * Find the count of Favourite instances by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @return the count of Favourite instances
	 */
	public long findSizeByProperty(String propertyName, Object value) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("select count(*) from ");
		sqlStringBuffer.append(Favourite.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append("= ?");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		Object results = queryObject.uniqueResult();
		return (results == null) ? 0 : ((Number) results).longValue();

	}
	


	/**
	 * Check whether a Favourite instance with the userName and message id
	 * exists
	 * 
	 * @param userName
	 *            username
	 * @param mid
	 *            message id
	 * @return true or false
	 */
	public boolean isAdded(String userName, Integer mid) {
		Object result = findBy2Properties(userName, mid);
		if (result != null)
			return true;
		return false;
	}

}